package com.bookmanger.server.mapper;

import com.bookmanger.pojo.Entity.OverdueBorrowRecord;
import com.bookmanger.pojo.Entity.PunishmentEntity;
import com.bookmanger.pojo.VO.PunishmentVO;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface FineMapper {
    @Insert("INSERT INTO punishment(reader_id, book_id, pun_cause, pun_time, pun_num) " +
           "VALUES(#{readerId}, #{bookId}, #{punCause}, #{punTime}, #{punNum})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    Integer insert(PunishmentEntity punishmentEntity);
    @Update("UPDATE punishment SET status = 1 WHERE id = #{id}")
    int payFineById(Integer id);
    @Select("SELECT * FROM punishment WHERE reader_id = #{readerId} ")
    List<PunishmentEntity> findUnpaidFinesByReaderId(Integer readerId);
    @Select("SELECT * FROM punishment WHERE id = #{id}")
    PunishmentEntity findById(Integer id);
    
    /**
     * 查询需要检查逾期的借阅记录
     * 条件：已批准借阅但未批准归还，且未罚款的记录
     */
    @Select("SELECT b.*, r.email as readerEmail, i.title as bookTitle " +
           "FROM borrow b " +
           "JOIN reader r ON b.reader_id = r.id " +
           "JOIN isbn_tb i ON b.isbn_id = i.id " +
           "LEFT JOIN punishment p ON b.id = p.book_id AND p.pun_cause LIKE '%逾期%' " +
           "WHERE b.borrow_manager_id IS NOT NULL " +
           "AND b.return_manager_id IS NULL " +
           "AND p.id IS NULL")
    List<OverdueBorrowRecord> findBorrowRecordsForOverdueCheck();
    
    /**
     * 查询读者的所有罚款记录
     */
    @Select("SELECT * FROM punishment WHERE reader_id = #{readerId}")
    List<PunishmentEntity> findAllFinesByReaderId(Integer readerId);
    
    /**
     * 分页查询所有罚款记录
     */
    @Select("SELECT * FROM punishment")
    List<PunishmentEntity> findAllFines();
    
    /**
     * 多表联查分页查询所有罚款记录详情（包含读者姓名和账号）
     */
    @Select("SELECT p.*, r.reader_number as readerNumber, r.name as readerName " +
           "FROM punishment p " +
           "JOIN reader r ON p.reader_id = r.id")
    List<PunishmentVO> findAllFinesWithReaderDetails();
    
    /**
     * 根据读者ID或姓名查询罚款记录详情
     */
    @Select("SELECT p.*, r.reader_number as readerNumber, r.name as readerName " +
           "FROM punishment p " +
           "JOIN reader r ON p.reader_id = r.id " +
           "WHERE r.reader_number LIKE CONCAT('%', #{keyword}, '%') OR r.name LIKE CONCAT('%', #{keyword}, '%')")
    List<PunishmentVO> findFinesByReaderIdOrName(@Param("keyword") String keyword);
}